﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Turtle8
{
    public partial class YamamTrackCard : UserControl
    {
        public static readonly string TableName = "Yamam";
        DataSet ds = new DataSet();
        SqlDataAdapter da;
        DataTable table;
        Dictionary<string, DataRow> dict; //key==armyID, val==key's Row
        IMofa mofa;

        public SqlDataAdapter SqlDataAdapter
        {
            get { return da; }
            set{ da = value; }
        }

        public YamamTrackCard(IMofa m)
        {
            InitializeComponent();
            this.Dock = DockStyle.Fill;

            mofa = m;
            dict = new Dictionary<string, DataRow>();

            string selectStr = CompanyManager.getSqlStringFromFile(@"\SELECT\YamamTrackCard.SELECT.sql");
            string updateStr = CompanyManager.getSqlStringFromFile(@"\UPDATE\YamamTrackCard.UPDATE.sql");
            string deleteStr = "DELETE FROM [Yamam] WHERE (armyID=@armyID AND startDate=@startDate)";
            string insertStr = "INSERT INTO [Yamam] VALUES (@armyID, @date , @startDate, @isAttended)";


            da = SqlManager.SqlDataAdapterFactory(selectStr);
            da.UpdateCommand = SqlManager.SqlCommandFactory(updateStr);
            da.DeleteCommand = SqlManager.SqlCommandFactory(deleteStr);
            da.InsertCommand = SqlManager.SqlCommandFactory(insertStr);

            
            ICollection<string> sqlParameterCollection = CompanyManager.parseSqlQueryParameters(updateStr);
            sqlParameterCollection.Distinct<string>();

            //adding parameters dynamically to the UPDATE SqlCommand
            foreach (string item in sqlParameterCollection)
            {
                string sItem = "@" + item;
                switch (item)
                {
                    case "armyID": 
                        da.UpdateCommand.Parameters.Add(CompanyManager.createSqlParameter(sItem, SqlDbType.NVarChar, DataRowVersion.Original, "armyID"));
                        da.DeleteCommand.Parameters.Add(CompanyManager.createSqlParameter(sItem, SqlDbType.NVarChar, DataRowVersion.Original, "armyID"));
                        da.InsertCommand.Parameters.Add(CompanyManager.createSqlParameter(sItem, SqlDbType.NVarChar, DataRowVersion.Original, "armyID"));
                        break;
                    case "startDate":
                        da.SelectCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date)).Value = mofa.StartDate;
                        da.UpdateCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date)).Value = mofa.StartDate;
                        da.DeleteCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date)).Value = mofa.StartDate;
                        da.InsertCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date)).Value = mofa.StartDate;
                        break;

                    case "date":
                        da.UpdateCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date));
                        da.InsertCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Date));
                        break;
                    case "isAttended":
                        da.UpdateCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Bit)).Value = false;
                        da.InsertCommand.Parameters.Add(new SqlParameter(sItem, SqlDbType.Bit)).Value = false;
                        break;

                }
            }

           refresh();
        }

        private void createTable()
        {
            table = new DataTable();
            int mofaDays = mofa.getTotalDays();
            DateTime firstDay = mofa.StartDate;

            DataColumn army_id_col = new DataColumn("armyID");
            DataColumn first_name_col = new DataColumn("firstName");
            DataColumn last_name_col = new DataColumn("lastName");            
            
            army_id_col.DataType = System.Type.GetType("System.String");
            first_name_col.DataType = System.Type.GetType("System.String");
            last_name_col.DataType = System.Type.GetType("System.String");
            army_id_col.DataType = System.Type.GetType("System.String");
            table.Columns.Add(army_id_col);
            table.Columns.Add(first_name_col);
            table.Columns.Add(last_name_col);

            DateTime dayCounter = firstDay;
            
            //adding columns as the num of days in Tahasuka
            for (int i = 0; i < mofaDays; i++)
            {
                DataColumn col = new DataColumn(dayCounter.Day.ToString() + @"/" + dayCounter.Month.ToString());
                dayCounter = dayCounter.AddDays(1);

                col.DataType = Type.GetType("System.Boolean");
                table.Columns.Add(col);
            }
        
           //inserting Rows to DataTable
            SqlCommand sqlCmd = SqlManager.SqlCommandFactory(CompanyManager.getSqlStringFromFile(@"\SELECT\TahasukaDuringYamamTrackCard.SELECT2.sql"));
           sqlCmd.Parameters.Add("@startDate",SqlDbType.Date).Value = mofa.StartDate;

           SqlDataReader reader = da.SelectCommand.ExecuteReader();
           while (reader.Read())
           {
               DataRow row = table.NewRow();
               if (!dict.ContainsKey((string)(reader["armyID"])))
               {
                   row[army_id_col] = (string)(reader["armyID"]);
                   if ((reader["firstName"]) == System.DBNull.Value)
                       row[first_name_col] = null;
                   else row[first_name_col] = (string)(reader["firstName"]);
                   if ((reader["lastName"]) == System.DBNull.Value)
                       row[last_name_col] = null;
                   else row[last_name_col] = (string)(reader["lastName"]);

                   table.Rows.Add(row);
                   dict.Add((string)row[army_id_col], row);
               }
           }

           reader.Close();

           
           //iterating over all the yamams of the isCalled==true soldiers to this mofa
           DataRowCollection rows = ds.Tables[0].Rows;
           DataColumnCollection cols = ds.Tables[0].Columns;
           foreach (DataRow row in rows)
	       {
               object []db_result_array =  row.ItemArray;
               string id = (string)db_result_array[cols.IndexOf("armyID")];
               DateTime date = (DateTime)db_result_array[cols.IndexOf("date")];
               
               // +3 because the struct of each row is :{armyId, firstName, lastName, day1 ,day2, ... , dayN} where N is total days of Tahasuka, including Alal
               int pos = date.Subtract(firstDay).Days; 
               DataRow r = dict[id];

               //this try/catch handles a null value case
               try
               {
                   r[pos + 3] = (bool)db_result_array[3];
               }
               catch (Exception)
               {
                   r[pos + 3] = false;
               }
	       }
        }


        public void refresh(object o, EventArgs e) { refresh(); }
        public void refresh()
        {
            dict.Clear();
            ds.Clear();

            //Populate the DataSet by running the Fill method of the SqlDataAdapter.
            da.Fill(ds, TableName);

            createTable();
            dataGridView.DataSource = table;
            dataGridView.Columns["firstName"].HeaderText = "שם פרטי";
            dataGridView.Columns["lastName"].HeaderText = "שם משפחה";
            dataGridView.Columns["armyID"].HeaderText = "מ.א.";

            dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                      
            dataGridView.Columns["firstName"].ReadOnly = true;
            dataGridView.Columns["lastName"].ReadOnly = true;
            dataGridView.Columns["armyID"].ReadOnly = true;

            dataGridView.Columns["firstName"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridView.Columns["lastName"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
            dataGridView.Columns["armyID"].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
        }

        private void TahasukaDuringYamamTrackCard_Load(object sender, EventArgs e)
        {
            refresh();
        }
        
    
        private void setRowsCheckBox(bool isCheck, System.Collections.IEnumerable rows)
        {
            int tot_days = mofa.getTotalDays();

            foreach (DataGridViewRow row in rows)
            {
                for (int i = 0; i < tot_days; i++)
                    row.Cells[i + 3].Value = isCheck;
            }
        }

        public override string ToString()
        {
            return @"מעקב ימ''מ";
        }

        private void toolStripMenuItemMarkAll_Click(object sender, EventArgs e)
        {
            setRowsCheckBox(true, dataGridView.Rows);
        }

        private void toolStripMenuItemDemarkAll_Click(object sender, EventArgs e)
        {
            setRowsCheckBox(false, dataGridView.Rows);
        }

        private void toolStripMenuItemDemarkSelectedRows_Click(object sender, EventArgs e)
        {
            System.Collections.IEnumerable selected_rows = dataGridView.SelectedRows;
            setRowsCheckBox(false, selected_rows);
        }

        private void toolStripMenuItemMarkSelectedRows_Click(object sender, EventArgs e)
        {
            System.Collections.IEnumerable selected_rows = dataGridView.SelectedRows;
            setRowsCheckBox(true, selected_rows);
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView.Rows)
            {
                int tot_days = mofa.getTotalDays();
                DateTime date = mofa.StartDate;

                da.UpdateCommand.Parameters["@armyID"].Value = (string)row.Cells["armyID"].Value;
                for (int i = 0; i < tot_days; i++)
                {
                    da.UpdateCommand.Parameters["@date"].Value = date;
                    da.UpdateCommand.Parameters["@isAttended"].Value = row.Cells[i + 3].Value;

                    SqlManager.executeNonQuery(da.UpdateCommand);
                    date = date.AddDays(1);
                }
            }
            labelSuccessfulUpdate.Visible = true;
            labelSuccessfulUpdate.Location = new Point(splitContainer.Panel1.Bounds.Width / 2, 16);
            timer.Start();
        }

        private void timer_Tick(object sender, EventArgs e)
        {
            labelSuccessfulUpdate.Visible = false;
        }

    }
}
